#!/usr/bin/python
# -*- coding: UTF-8 -*-
# author : bird.zhang@ximalaya.com

import codecs
import os
import random
import zlib

# =====================
# 设置参数 BEGIN
# =====================

db_name = 'microlesson'
table_name = 'tb_favorite'
table_cnt = 128
file_name = "%s_shard_by_node.sql" % table_name

sql_template = """
DROP TABLE IF EXISTS `{db_name}`.`{table_name}{table_index}`;
CREATE TABLE `{db_name}`.`{table_name}{table_index}` (
  `id` bigint(20) NOT NULL COMMENT '主键id',
  `uid` bigint(20) NOT NULL COMMENT '用户id',
  `type` int(11) NOT NULL COMMENT '1:系列课,2:课程',
  `biz_id` bigint(20) NOT NULL COMMENT '根据type，具体类型的id',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uni_uid_type_biz_id` (`uid`,`type`,`biz_id`),
  KEY `ind_uid_created_at` (`uid`,`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='根据uid进行分表;硬删，删除操作，记tb_favorite_log表';
"""

# ============
# 设置参数 END
# ============

if os.path.isfile(file_name):
    print("file %s has exist, remove it!" % file_name)
    os.remove(file_name)

# file = open(file_name, 'w')

file = codecs.open(file_name, "w", "utf-8")

# =====================
# 先生成一份原始不加后缀的
# =====================

sql = sql_template.replace('{db_name}', db_name).replace('{table_name}', table_name).replace('{table_index}', '')
print(sql)
file.write(sql)

# =====================
# 生成表格
# =====================

for table_index in range(table_cnt):
    sql = sql_template.replace('{db_name}', db_name).replace('{table_name}', table_name).replace('{table_index}',
                                                                                                 str(table_index))
    print(sql)
    file.write(sql)

# =====================
# 生成node表
# =====================

create_node_table_template = """
DROP TABLE IF EXISTS `{db_name}`.`{table_name}_node`;
CREATE TABLE `{db_name}`.`{table_name}_node`(
	`id` INT(11) NOT NULL AUTO_INCREMENT, 
	`node` INT(11) UNSIGNED NOT NULL, 
	`table_name` VARCHAR(255) NOT NULL, 
	`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, 
	`updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
	PRIMARY KEY (`id`), 
	UNIQUE KEY `idx_node` (`node`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
"""

sql = create_node_table_template.replace('{db_name}', db_name).replace('{table_name}', table_name)
print(sql)
file.write(sql)


# =====================
# node表插值
# =====================


def gen_random_len():
    return int(random.random() * 9 + 8)


hex_chars = ['0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'a', 'b', 'c', 'd', 'e', 'f']


def gen_random_hex_str(str_len):
    _str = ''
    for i in range(str_len):
        _index = int(random.random() * len(hex_chars))
        _str += str(hex_chars[_index])

    return _str


insert_node_table_template = """
INSERT INTO `{db_name}`.`{table_name}_node` (node, table_name) values ({node_value}, '{table_name}{table_index}');
"""

for table_index in range(table_cnt):
    for j in range(10):
        str_len = gen_random_len()
        random_hex_str = gen_random_hex_str(str_len)
        node_value = zlib.crc32(bytes(random_hex_str, encoding="utf8"))
        sql = insert_node_table_template.replace('{db_name}', db_name).replace('{table_name}', table_name).replace(
            '{node_value}', str(node_value)).replace('{table_index}', str(table_index))
        print(sql)
        file.write(sql)

file.close()
